package com.zsl.dao.impl;

import com.mysql.cj.xdevapi.SqlResultImpl;
import com.zsl.dao.UsersDao;
import com.zsl.model.Users;
import com.zsl.util.C3P0Util;
import com.zsl.util.DbHelper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDaoImpl implements UsersDao {
    @Override
    public Users getUserByName(String uname, String pwd) {
        Users user = null;
        try {
            String sql = "select * from users where uname=? and pwd=?";
            //防止SQL注入
            String[] params = {uname, pwd};
            ResultSet rs = DbHelper.executeQuery(sql, params);
            if (rs.next()) {
                // 封装
                user = new Users();
                user.setUid(rs.getInt("uid"));
                user.setUname(rs.getString("uname"));
                user.setPwd(rs.getString("pwd"));
                user.setHobby(rs.getString("hobby"));
            }
            //JdbcUtil.release(DbHelper.getPs(), DbHelper.getConn(), rs);
            C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return user;
    }


    @Override
    public List<Users> getAllUsers() {
        List<Users> users = null;
        Users user = null;

        try {
            String sql = "SELECT * FROM users where is_deleted=0";
            ResultSet rs = DbHelper.executeQuery(sql, null);
            users = new ArrayList<Users>();
            while (rs.next()) {
                user = new Users();

                user.setUid(rs.getInt("uid"));
                user.setUname(rs.getString("uname"));
                user.setHobby(rs.getString("hobby"));
                user.setPwd(rs.getString("pwd"));

                users.add(user);
            }
            C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return users;
    }

    @Override
    public List<Users> queryBySQL(String sql) {
        List<Users> users = null;
        Users user = null;

        try {
            ResultSet rs = DbHelper.executeQuery(sql, null);
            users = new ArrayList<Users>();
            while (rs.next()) {
                user = new Users();

                user.setUid(rs.getInt("uid"));
                user.setUname(rs.getString("uname"));
                user.setHobby(rs.getString("hobby"));
                user.setPwd(rs.getString("pwd"));

                users.add(user);
            }
            C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return users;
    }

    @Override
    public int deleteUserByUid(int uid) {
        int line = 0;
        String sql = "UPDATE users SET is_deleted='1' WHERE uid=?";
        try {
            line = DbHelper.executeUpdate(sql, new Object[]{uid});
        } catch (Exception e) {
            e.printStackTrace();
        }
        return line;
    }

    @Override
    public void addUser(Users user) throws Exception {
        // 与数据库打交道
        String sql = "INSERT INTO users (uid,uname,pwd,hobby,is_deleted) VALUES(null,?,?,?,'0')";
        try {
            DbHelper.executeUpdate(sql, new Object[]{user.getUname(), user.getPwd(), user.getHobby()});
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception();
        }
    }

    @Override
    public String getAllUserNum() throws Exception {
        String result;
        String total = null;
        String sql = "SELECT Count(*) FROM users WHERE is_deleted=0";
        try {
            ResultSet rs = DbHelper.executeQuery(sql, null);
            rs.next();
            total = rs.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception();
        } finally {
            result = total;
        }
        return result;
    }

    @Override
    public int UpdateUserInfo(Users user) {
        return DbHelper.update("users", new String[]{"uname", "hobby"}, new String[]{user.getUname(),
                user.getHobby()}, new String[]{"uid"}, new String[]{user.getUid().toString()});
    }

    //TODO 实现
    @Override
    public List<Users> getUsersBySearchStr(String searchStr) throws Exception {
        searchStr = "%" + searchStr + "%";
        String sql = "SELECT * FROM users WHERE uname LIKE ? AND is_deleted=0";
        List<Users> users = null;
        Users user = null;

        users = new ArrayList<Users>();
        ResultSet rs = DbHelper.executeQuery(sql, new Object[]{searchStr});
        while (rs.next()) {
            user = new Users();

            user.setUid(rs.getInt("uid"));
            user.setUname(rs.getString("uname"));
            user.setHobby(rs.getString("hobby"));
            user.setPwd(rs.getString("pwd"));

            users.add(user);
        }
        C3P0Util.release(DbHelper.getPs(), DbHelper.getConn(), rs);
        return users;
    }

}
